package com.iwunu.gateway.repository;

import java.util.List;
import java.util.Map;
import javax.transaction.Transactional;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import com.iwunu.gateway.entity.FLock;
import com.iwunu.gateway.entity.FLockMember;
import io.lettuce.core.dynamic.annotation.Param;

/**
 * @author kinfeng
 * @date 2021/5/21 10:00 AM
 * @desc
 */
public interface FaceLocMemberRepository extends JpaRepository<FLockMember, Long> , JpaSpecificationExecutor<FLockMember> {
	
	   @Query(value="select u.id AS userId,u.avatar,u.nickName,i.lockClientId,i.lockId,i.id AS lockMemberId,IFNULL(l.`name`,'人脸锁') as `name`,IFNULL(l.electric,100) as electric,l.netWorkStatus, \r\n" + 
	   		"IFNULL(l.location,'中国')  AS location,IFNULL(l.signalInfo,'很好')  as signalInfo,i.role,i.remark  from t_lock l,t_flock_member i left join t_flock_user u on i.userId=u.id  \r\n" + 
	   		"where l.id = i.lockId and l.isDel=false  AND i.lockId=?1 and i.isDel =false and i.role is not null and i.role != 10 ",nativeQuery = true)
	   List<Map<String, Object>> selectFaceLockMembers(@Param("lockId") Integer lockId);
	   
	   @Query(value="select u.id AS userId,u.avatar,u.nickName,i.lockClientId,i.lockId,i.id AS lockMemberId,IFNULL(l.`name`,'人脸锁') as `name`,IFNULL(l.electric,100) as electric,l.netWorkStatus,l.mac,l.baseCode,\r\n" + 
	   		"IFNULL(l.location,'中国')  AS location,IFNULL(l.signalInfo,'很好')  as signalInfo ,i.role,i.remark  from t_lock l, t_flock_member i left join t_flock_user u on i.userId=u.id   \r\n" + 
	   		"where l.id = i.lockId and l.isDel=false  and i.lockId=?1 and i.isDel =false and i.role is null or i.role =10 ",nativeQuery = true)
	   List<Map<String, Object>> authList(@Param("lockId") Integer lockId);
	   
	   @Query(value=" SELECT l.id AS lockId,IFNULL(l.`name`,'人脸锁') as `name`,l.clientId,IFNULL(l.location,'中国')  AS location , l.createTime,l.netWorkStatus,l.mac,l.baseCode, \r\n" + 
	   		"m.role,m.remark,IFNULL(l.signalInfo,'很好') AS signalInfo,IFNULL(l.electric,80) AS electric FROM t_lock l ,t_flock_member m WHERE m.userId =?1  AND l.id = m.lockId and m.role in(1,2,3) and l.isDel=false and m.isDel=0 ",nativeQuery = true)
	   List<Map<String, Object>> selectByUserFaceLocks(@Param("userId") Integer userId);
	   
	   @Query(value=" SELECT l.id AS lockId,IFNULL(l.`name`,'人脸锁') as `name`,l.clientId,IFNULL(l.location,'中国')  AS location , l.createTime,l.netWorkStatus,l.mac,l.baseCode, \r\n"
	   		+ "IFNULL(l.signalInfo,'很好') AS signalInfo,IFNULL(l.electric,80) AS electric FROM t_lock l  WHERE l.bussinessId =?1  and l.isDel=false",nativeQuery = true)
		   Page<FLock> selectByAdminFaceLocks(@Param("bussinessId") String bussinessId, Pageable pageable);
		   
	   
	   
	   @Query(value=" SELECT l.id AS lockId,IFNULL(l.`name`,'人脸锁') as `name`,l.clientId,IFNULL(l.location,'中国')  AS location ,l.netWorkStatus,l.mac,l.baseCode,\r\n" + 
		   		"m.role,IFNULL(l.signalInfo,'很好') AS signalInfo,IFNULL(l.electric,80) AS electric,m.remark FROM t_lock l ,t_flock_member m WHERE m.userId =?1 and l.isDel=false AND l.id = m.lockId and m.role =1 ",nativeQuery = true)
		   List<Map<String, Object>> selectMyFaceLocks(@Param("userId") Integer userId);
		
	   @Query(value="select u.id AS userId,u.avatar,u.nickName,i.lockClientId,i.lockId,i.id AS lockMemberId,\n" + 
	   		"i.role,i.remark  from t_flock_member i left join t_flock_user u on i.userId=u.id \n" + 
	   		"where i.lockId=?1 and i.isDel =false and i.role is not null and i.role != 10 and role > ?2 ",nativeQuery = true)
		   List<Map<String, Object>> selectRoleMembers(@Param("lockId") Integer lockId,@Param("role") Integer role);
	   
	   @Query(value="select u.id AS userId,u.avatar,u.nickName,i.lockClientId,i.lockId,i.id AS lockMemberId,\n" + 
		   		"i.role,i.remark  from t_flock_member i left join t_flock_user u on i.userId=u.id \n" + 
		   		"where i.lockId=?1 and i.isDel =false and i.role is not null and i.role != 10 ",nativeQuery = true)
			   List<Map<String, Object>> selectMembers(@Param("lockId") Integer lockId);
		      
	   @Transactional
	   @Modifying
	   @Query(value="update FLockMember i set i.isDel=true where  i.userId=:userId and lockId =:lockId")
	   int updateByLockIdAndUserId(@Param("userId") Integer userId,@Param("lockId") Integer lockId);
	   
	   @Transactional
	   @Modifying
	   @Query(value="update FLockMember i set i.isDel=true where  i.id=:lockMemberId  ")
	   int delByLockMemberId(@Param("lockMemberId") Long lockMemberId);
	   
	   @Transactional
	   @Modifying
	   @Query(value="update FLockMember i set i.role=:role where  i.id=:lockMemberId ")
	   int auth(@Param("lockMemberId") Long lockMemberId,@Param("role") Integer role);
	   
	   @Query(value=" SELECT i from FLockMember i where i.userId =:userId and i.lockId =:lockId and i.isDel=false ")
	   FLockMember findFaceLockMember(@Param("userId") Integer userId,@Param("lockId") Integer lockId);
	   /**
	    * 商户ID查询Lock
	    * @param userId
	    * @param lockId
	    * @return
	    */
	   @Query(value=" SELECT i from FLockMember i where i.id =:id  ")
	   FLockMember findMemberById(@Param("id") Long id);
	   
	   @Transactional
	   @Modifying
	   @Query(value="update FLockMember i set i.isDel= true where  i.lockId=:lockId  ")
	   int delByLockId(@Param("lockId") Integer lockId);
	   
	   @Query(value="select u.id AS userId,u.avatar,u.nickName,i.lockClientId,i.lockId,i.id AS lockMemberId ,IFNULL(l.`name`,'人脸锁') as `name`,IFNULL(l.electric,100) as electric,l.netWorkStatus,l.type ," + 
	   		"IFNULL(l.location,'中国')  AS location,IFNULL(l.signalInfo,'很好') as signalInfo,l.adminPassword,l.createTime,l.mac,l.baseCode,i.role,i.remark  from t_lock l, t_flock_member i left join t_flock_user u on i.userId=u.id  \r\n" + 
	   		"where l.id = i.lockId AND  i.userId=?1 and i.lockId=?2 and i.isDel = false ",nativeQuery = true)
	   Map<String, Object>  findLockInfo(@Param("userId") Integer userId,@Param("lockId") Integer lockId);
	   
	   @Transactional
	   @Modifying 
	   @Query(value="update t_flock_member  set userId =?2 where  lockId in (?1) and role = 1 and isDel = false " ,nativeQuery = true)
	   int changeMember(@Param("lockIds")List<Integer> lockIds,@Param("userId") Integer userId);
	   
	   @Transactional
	   @Modifying
	   @Query(value="update t_flock_member  set isDel = 1 where  lockId in (?1) and role != 1 " ,nativeQuery = true)
	   int delByLockIds(@Param("lockIds")List<Integer> lockIds);
}
